﻿using System;
using System.Collections.Generic;
using System.Text;
using BarCode.Bean;
using BarCode.Common;
using System.Data;
using BarCode.Module;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;

namespace BarCode.AccessDao
{
    public class DB01Dao
    {
        private static readonly log4net.ILog LogUtil = log4net.LogManager.GetLogger(typeof(DB01Dao));

        private MySqlHelper db01;
        public DB01Dao(string serverIp, string userId, string userPwd)
        {
            db01 = new MySqlHelper(serverIp, "db_01", userId, userPwd);
        }

        /// <summary>
        /// 判断是否可链接
        /// </summary>
        /// <returns></returns>
        public bool EnableConnect()
        {
            return db01.EnableConnect();
        }



        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        public List<UserBean> getUserInfo(string lineNo)
        {
            List<UserBean> retList = new List<UserBean>();
            MySqlParameter sqlParam = new MySqlParameter("@lineNo", lineNo);
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from client_user where lineNo=@lineNo", new MySqlParameter[] { sqlParam });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    UserBean tmpBean = new UserBean();
                    tmpBean.User = tmpRow["username"].ToString();
                    tmpBean.Password = tmpRow["userpassword"].ToString();
                    tmpBean.Level = tmpRow["level"].ToString();
                    tmpBean.lineno = tmpRow["lineNo"].ToString();
                    retList.Add(tmpBean);
                }
            }
            return retList;
        }


        /// <summary>
        /// 获取指定用户当日的工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getMpWorkAmount(string userName, bool fiterRepair = true)
        {
            MySqlParameter param = new MySqlParameter("@Operator_Name", userName);
            string amountSql = "select count(Operator_Name) from " + GlobalVal.CUR_TBL_NAME + " where Operator_Name=@Operator_Name and date(OpDt) = CURDATE()";
            if (fiterRepair)
            {
                amountSql += " and mark_repair = '0' ";
            }
            LogUtil.Debug("获取工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取指定用户当日的工作量(物联网扫码)
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getGasWorkAmount(string userName)
        {
            MySqlParameter param = new MySqlParameter("@tmz_operator", userName);
            string amountSql = "select count(tmz_operator) from " + GlobalVal.CUR_TBL_NAME + " where tmz_operator=@tmz_operator";

            LogUtil.Debug("获取工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取指定用户当日的工作量（透明罩安装）
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getTMZWorkAmount(string userName)
        {
            MySqlParameter param = new MySqlParameter("@Operator_Name", userName);
            string amountSql = "select count(operator_name) from " + GlobalVal.CUR_TBL_NAME + " where operator_name=@operator_name";
            // and tmz_flag = '1'
            LogUtil.Debug("获取工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取表号和机壳码是否完全一致 透明罩
        /// </summary>
        /// <returns></returns>
        public int getNoAndCode(string txtNo)
        {
            MySqlParameter param = new MySqlParameter("@body_no", txtNo);
            string amountSql = "select count(*) FROM tbl_gas_code where body_no = @body_no";
            // and tmz_flag = '1'
            LogUtil.Debug("获取工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, param);
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取表号是否一致 透明罩
        /// </summary>
        /// <returns></returns>
        public int checkMetersNo(string inputBarCode)
        {
            MySqlParameter param = new MySqlParameter("@meters_no", inputBarCode);
            string amountSql = "select count(*) FROM tbl_gas_code where meters_no = @meters_no ";
            // and tmz_flag = '1'
            LogUtil.Debug("获取工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, param);
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取机壳码是否一致 透明罩
        /// </summary>
        /// <returns></returns>
        public int checkBodyNo(string txtNo)
        {
            MySqlParameter param = new MySqlParameter("@body_no", txtNo);
            string amountSql = "select count(*) FROM tbl_gas_code where body_no = @body_no ";
            // and tmz_flag = '1'
            LogUtil.Debug("获取工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, param);
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 检查是否透明罩安装
        /// </summary>
        /// <param name="mpBarCodeId"></param>
        /// <returns></returns>
        public string getTMZOperator(string mpBarCodeId)
        {
            MySqlParameter param = new MySqlParameter("@meters_no", mpBarCodeId);
            string getSql = "select tmz_operator from tbl_gas_code where meters_no = @meters_no";
            object tmzOperator = db01.ExecuteScalar(CommandType.Text, getSql, new MySqlParameter[] { param });
            if (tmzOperator == null)
            {
                return "表号不存在";

            }
            else
            {
                if ("".Equals(tmzOperator.ToString()))
                {
                    return "表号错误";
                }
                return tmzOperator.ToString();
            }
        }

        /// <summary>
        /// 根据扫码条码查询上一工序的操作者
        /// </summary>
        /// <param name="mpBarCodeId"></param>
        /// <returns></returns>
        public string getPreUser(string barcodeID)
        {
            MySqlParameter param = new MySqlParameter("@barcodeID", barcodeID);
            string getSql = "select Operator_Name from (select * from " + GlobalVal.PRE_TBL_NAME + " order by OpDt desc limit 0," + GlobalVal.PRE_EXIST_RECORD + " ) v where " + GlobalVal.PRE_FIELD + " = @barcodeID";
            LogUtil.Info("前道工序查询SQL：" + getSql);
            object preUser = db01.ExecuteScalar(CommandType.Text, getSql, new MySqlParameter[] { param });
            if (preUser == null)
            {
                return null;

            }
            else
            {
                return preUser.ToString();
            }
        }

        /// <summary>
        /// 根据扫码条码查询上一工序的操作者
        /// </summary>
        /// <param name="mpBarCodeId"></param>
        /// <returns></returns>
        public string getTMZUser(string barcodeID)
        {
            MySqlParameter param = new MySqlParameter("@meters_no", barcodeID);
            string getSql = "select operator_name from (select * from tbl_gas_code order by Opt_date desc limit 0," + GlobalVal.PRE_EXIST_RECORD + " ) v where meters_no = @meters_no";
            LogUtil.Info("前道工序查询SQL：" + getSql);
            object preUser = db01.ExecuteScalar(CommandType.Text, getSql, new MySqlParameter[] { param });
            if (preUser == null)
            {
                return null;

            }
            else
            {
                return preUser.ToString();
            }
        }




        /// <summary>
        /// 判断本道工序是否重复
        /// </summary>
        /// <param name="barcodeID"></param>
        /// <returns></returns>
        public bool isRepeat(string barcodeID)
        {
            string existSql = "select count(1) from (select * from " + GlobalVal.CUR_TBL_NAME + " order by OpDt desc limit 0," + GlobalVal.CUR_REPEAT_RECORD + " ) v where " + GlobalVal.CUR_FIELD + " = @barcodeID ";
            MySqlParameter existParam = new MySqlParameter("@barcodeID", barcodeID);
            object existRes = db01.ExecuteScalar(CommandType.Text, existSql, new MySqlParameter[] { existParam });

            if (Convert.ToInt32(existRes) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 判断本道工序是否重复
        /// </summary>
        /// <param name="barcodeID"></param>
        /// <returns></returns>
        public bool isGasRepeat(string barcodeID)
        {
            string existSql = "select count(1) from (select * from " + GlobalVal.CUR_TBL_NAME + " order by Opt_date desc limit 0," + GlobalVal.CUR_REPEAT_RECORD + " ) v where " + GlobalVal.CUR_FIELD + " = @barcodeID ";
            MySqlParameter existParam = new MySqlParameter("@barcodeID", barcodeID);
            object existRes = db01.ExecuteScalar(CommandType.Text, existSql, new MySqlParameter[] { existParam });

            if (Convert.ToInt32(existRes) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 查询表号
        /// </summary>
        /// <param name="meterNo"></param>
        /// <returns></returns>
        public GasCodeInfo getGasInfo(string meterNo)
        {
            string getSql = "select ID, body_no,meters_no,operator_name from tbl_gas_code where meters_no = @meters_no order by Opt_date desc limit 0,1 ";
            MySqlParameter getParam = new MySqlParameter("@meters_no", meterNo);
            DataSet dsGas = db01.GetDataSet(CommandType.Text, getSql, new MySqlParameter[] { getParam });

            if (dsGas == null || dsGas.Tables.Count == 0 || dsGas.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            else
            {
                GasCodeInfo gasCodeInfo = new GasCodeInfo();
                DataRow drGas = dsGas.Tables[0].Rows[0];
                gasCodeInfo.ID = Convert.ToInt32(drGas["ID"]);
                gasCodeInfo.body_no = Convert.ToString(drGas["body_no"]);
                gasCodeInfo.meters_no = Convert.ToString(drGas["meters_no"]);
                gasCodeInfo.operator_name = Convert.ToString(drGas["operator_name"]);
                return gasCodeInfo;
            }
        }



        /// <summary>
        /// 保存1.底座安装工序记录
        /// </summary>
        /// <param name="jsqBarcodeID"></param>
        /// <returns></returns>
        public int saveDZBarCode(string jsqBarcodeID)
        {
            try
            {
                string insSql = "INSERT INTO dz_barcode (jsq_barcodeID, Operator_Name, OpDt, mark_repair) VALUES (@barcodeID, @Operator_Name, @OpDt, @mark_repair) ";
                string existSql = "select count(1) from dz_barcode where jsq_barcodeID = @barcodeID ";

                MySqlParameter existParam = new MySqlParameter("@barcodeID", jsqBarcodeID);
                object existRes = db01.ExecuteScalar(CommandType.Text, existSql, new MySqlParameter[] { existParam });

                MySqlParameter[] sqlParams = new MySqlParameter[4];
                sqlParams[0] = new MySqlParameter("@barcodeID", jsqBarcodeID);
                sqlParams[1] = new MySqlParameter("@Operator_Name", GlobalVal.CurrentUser.User);
                sqlParams[2] = new MySqlParameter("@OpDt", DateTime.Now);

                if (Convert.ToInt32(existRes) > 0)
                {
                    // 存在则属于返修
                    sqlParams[3] = new MySqlParameter("@mark_repair", "1");
                }
                else
                {
                    // 不存在
                    sqlParams[3] = new MySqlParameter("@mark_repair", "0");
                }
                int cnt = db01.ExecuteNonQuery(CommandType.Text, insSql, sqlParams);
                if (cnt == 1)
                {
                    return Consts.RET_SUCCESS;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error("异常发生", ex);
            }

            return Consts.RET_FAIL;
        }

        /// <summary>
        /// 保存2.主控板安装工序记录
        /// </summary>
        /// <param name="jsqBarcodeID"></param>
        /// <returns></returns>
        public int saveZKBBarCode(string jsqBarcodeID)
        {
            try
            {
                string insSql = "INSERT INTO zkb_barcode (jsq_barcodeID, Operator_Name, OpDt, mark_repair) VALUES (@barcodeID, @Operator_Name, @OpDt, @mark_repair) ";
                string existSql = "select count(1) from zkb_barcode where jsq_barcodeID = @barcodeID ";

                MySqlParameter existParam = new MySqlParameter("@barcodeID", jsqBarcodeID);
                object existRes = db01.ExecuteScalar(CommandType.Text, existSql, new MySqlParameter[] { existParam });

                MySqlParameter[] sqlParams = new MySqlParameter[4];
                sqlParams[0] = new MySqlParameter("@barcodeID", jsqBarcodeID);
                sqlParams[1] = new MySqlParameter("@Operator_Name", GlobalVal.CurrentUser.User);
                sqlParams[2] = new MySqlParameter("@OpDt", DateTime.Now);

                if (Convert.ToInt32(existRes) > 0)
                {
                    // 存在则属于返修
                    sqlParams[3] = new MySqlParameter("@mark_repair", "1");
                }
                else
                {
                    // 不存在
                    sqlParams[3] = new MySqlParameter("@mark_repair", "0");
                }
                int cnt = db01.ExecuteNonQuery(CommandType.Text, insSql, sqlParams);
                if (cnt == 1)
                {
                    return Consts.RET_SUCCESS;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error("异常发生", ex);
            }

            return Consts.RET_FAIL;
        }

        /// <summary>
        /// 3. 物联网扫码保存
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public int saveGasCode(GasCodeInfo info)
        {
            try
            {
                string insertSql = "INSERT INTO tbl_gas_code (body_no, meters_no, did_code, imei_code, iccid_code, Opt_date,network_operator, operator_name, meter_direction) VALUES (@body_no, @meters_no, @did_code, @imei_code, @iccid_code, @Opt_date, @network_operator, @operator_name, @meter_direction) ";
                MySqlParameter[] sqlParams = new MySqlParameter[9];
                sqlParams[0] = new MySqlParameter("@body_no", info.body_no);
                sqlParams[1] = new MySqlParameter("@meters_no", info.meters_no);
                sqlParams[2] = new MySqlParameter("@did_code", info.did_code);
                sqlParams[3] = new MySqlParameter("@imei_code", info.imei_code);
                sqlParams[4] = new MySqlParameter("@iccid_code", info.iccid_code);
                sqlParams[5] = new MySqlParameter("@Opt_date", info.Opt_date);
                sqlParams[6] = new MySqlParameter("@network_operator", info.network_operator);
                sqlParams[7] = new MySqlParameter("@operator_name", info.operator_name);
                sqlParams[8] = new MySqlParameter("@meter_direction", info.meter_direction);

                return db01.ExecuteNonQuery(CommandType.Text, insertSql, sqlParams);
            }
            catch (Exception ex)
            {
                LogUtil.Error("数据库保存失败", ex);
                return Consts.RET_FAIL;
            }

        }
        
        //修改透明罩 表号一致
        public int updateGasCode(GasCodeInfo info)
        {
            try
            {
                string insertSql = "UPDATE tbl_gas_code SET body_no = @body_no, Opt_date = @Opt_date,operator_name = @operator_name WHERE meters_no = @meters_no";
                MySqlParameter[] sqlParams = new MySqlParameter[4];
                sqlParams[0] = new MySqlParameter("@body_no", info.body_no);
                sqlParams[1] = new MySqlParameter("@meters_no", info.meters_no);
                sqlParams[2] = new MySqlParameter("@Opt_date", info.Opt_date);
                sqlParams[3] = new MySqlParameter("@operator_name", info.operator_name);

                return db01.ExecuteNonQuery(CommandType.Text, insertSql, sqlParams);
            }
            catch (Exception ex)
            {
                LogUtil.Error("数据库保存失败", ex);
                return Consts.RET_FAIL;
            }

        }

        //修改透明罩 机壳码一致
        public int updateTxtNo(GasCodeInfo info)
        {
            try
            {
                string insertSql = "UPDATE tbl_gas_code SET meters_no = @meters_no, Opt_date = @Opt_date,operator_name = @operator_name WHERE body_no = @body_no";
                MySqlParameter[] sqlParams = new MySqlParameter[4];
                sqlParams[0] = new MySqlParameter("@body_no", info.body_no);
                sqlParams[1] = new MySqlParameter("@meters_no", info.meters_no);
                sqlParams[2] = new MySqlParameter("@Opt_date", info.Opt_date);
                sqlParams[3] = new MySqlParameter("@operator_name", info.operator_name);

                return db01.ExecuteNonQuery(CommandType.Text, insertSql, sqlParams);
            }
            catch (Exception ex)
            {
                LogUtil.Error("数据库保存失败", ex);
                return Consts.RET_FAIL;
            }

        }

        //修改上盖
        public int updateSGCode(GasCodeInfo info)
        {
            try
            {
                string insertSql = "UPDATE tbl_gas_code SET tmz_operator = @tmz_operator, tmz_date = @tmz_date WHERE meters_no = @meters_no";
                MySqlParameter[] sqlParams = new MySqlParameter[3];
                sqlParams[0] = new MySqlParameter("@tmz_operator", info.operator_name);
                sqlParams[1] = new MySqlParameter("@tmz_date", info.Opt_date);
                sqlParams[2] = new MySqlParameter("@meters_no", info.meters_no);
                return db01.ExecuteNonQuery(CommandType.Text, insertSql, sqlParams);
            }
            catch (Exception ex)
            {
                LogUtil.Error("数据库保存失败", ex);
                return Consts.RET_FAIL;
            }

        }

        ///// <summary>
        ///// 保存4.透明罩（铭牌）安装工序记录
        ///// </summary>
        ///// <param name="jsqBarcodeID"></param>
        ///// <returns></returns>
        //public int saveMPBarCode(string jsqBarcodeID, string mpBarcodeID)
        //{
        //    try
        //    {
        //        string insSql = "INSERT INTO jsqmp_barcode (jsq_barcodeID, mp_barcodeID, Operator_Name, OpDt, mark_repair) VALUES (@jsq_barcodeID, @mp_barcodeID, @Operator_Name, @OpDt, @mark_repair) ";
        //        string existSql = "select count(1) from jsqmp_barcode where jsq_barcodeID = @jsq_barcodeID ";

        //        MySqlParameter existParam = new MySqlParameter("@jsq_barcodeID", jsqBarcodeID);
        //        object existRes = db01.ExecuteScalar(CommandType.Text, existSql, new MySqlParameter[] { existParam });

        //        MySqlParameter[] sqlParams = new MySqlParameter[5];
        //        sqlParams[0] = new MySqlParameter("@jsq_barcodeID", jsqBarcodeID);
        //        sqlParams[1] = new MySqlParameter("@mp_barcodeID", mpBarcodeID);
        //        sqlParams[2] = new MySqlParameter("@Operator_Name", GlobalVal.CurrentUser.User);
        //        sqlParams[3] = new MySqlParameter("@OpDt", DateTime.Now);

        //        if (Convert.ToInt32(existRes) > 0)
        //        {
        //            // 存在则属于返修
        //            sqlParams[4] = new MySqlParameter("@mark_repair", "1");
        //        }
        //        else
        //        {
        //            // 不存在
        //            sqlParams[4] = new MySqlParameter("@mark_repair", "0");
        //        }
        //        int cnt = db01.ExecuteNonQuery(CommandType.Text, insSql, sqlParams);
        //        if (cnt == 1)
        //        {
        //            return Consts.RET_SUCCESS;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        LogUtil.Error("异常发生", ex);
        //    }

        //    return Consts.RET_FAIL;
        //}

        /// <summary>
        /// 保存4.透明罩（铭牌）安装工序记录 （物联网）
        /// </summary>
        /// <param name="jsqBarcodeID"></param>
        /// <returns></returns>
        public int saveTMZBarCode(GasCodeInfo gasInfo)
        {
            try
            {
                string updSql = "update tbl_gas_code set tmz_flag = '1', tmz_operator=@tmz_operator, tmz_date=@tmz_date where ID = @ID";

                MySqlParameter[] sqlParams = new MySqlParameter[3];
                sqlParams[0] = new MySqlParameter("@tmz_operator", GlobalVal.CurrentUser.User);
                sqlParams[1] = new MySqlParameter("@tmz_date", DateTime.Now);
                sqlParams[2] = new MySqlParameter("@ID", gasInfo.ID);

                int updCnt = db01.ExecuteNonQuery(CommandType.Text, updSql, sqlParams);
                if (updCnt == 1)
                {
                    return Consts.RET_SUCCESS;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error("异常发生", ex);
            }

            return Consts.RET_FAIL;
        }

        /// <summary>
        /// 保存5.上盖安装工序记录
        /// </summary>
        /// <param name="mpBarCodeId"></param>
        /// <returns></returns>
        public int saveSGBarCode(string mpBarCodeId)
        {
            try
            {
                string insSql = "INSERT INTO mp_barcode (mp_barcodeID, Operator_Name, OpDt) VALUES (@barcodeID, @Operator_Name, @OpDt) ";
                string existSql = "select count(1) from mp_barcode where mp_barcodeID = @barcodeID ";
                string updSql = "UPDATE mp_barcode SET Operator_Name = @Operator_Name, OpDt = @OpDt WHERE mp_barcodeID = @barcodeID ";

                MySqlParameter existParam = new MySqlParameter("@barcodeID", mpBarCodeId);
                object existRes = db01.ExecuteScalar(CommandType.Text, existSql, new MySqlParameter[] { existParam });

                MySqlParameter[] sqlParams = new MySqlParameter[3];
                sqlParams[0] = new MySqlParameter("@barcodeID", mpBarCodeId);
                sqlParams[1] = new MySqlParameter("@Operator_Name", GlobalVal.CurrentUser.User);
                sqlParams[2] = new MySqlParameter("@OpDt", DateTime.Now);

                int cnt = 0;
                if (Convert.ToInt32(existRes) > 0)
                {
                    // 存在则更新
                    cnt = db01.ExecuteNonQuery(CommandType.Text, updSql, sqlParams);
                }
                else
                {
                    // 服务器数据库不存在，执行插入操作
                    cnt = db01.ExecuteNonQuery(CommandType.Text, insSql, sqlParams);
                }
                if (cnt == 1)
                {
                    return Consts.RET_SUCCESS;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error("异常发生", ex);
            }

            return Consts.RET_FAIL;
        }


    }
}

